package com.ai.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.LogFactory;

public class DBUtil {
	private static org.apache.commons.logging.Log log = LogFactory.getLog(DBUtil.class);

	public static String queryForString(Connection conn, String sql, Object[] params)
			throws Exception {
		Object oo = queryForValue(conn, sql, params);
		String re = "";
		if (oo != null) {
			re = String.valueOf(oo);
		}
		return re;
	}
	
	public static List queryForSingleList(Connection conn, String sql, Object[] params) throws Exception{
		List reList = new ArrayList();
		List<Map<String, Object>> list = getQueryMap(conn, sql, params);
		if (list != null) {
			for(Map<String, Object> map :list){
				Collection cc = map.values();
				Object re = null;
				if (cc != null && !cc.isEmpty()) {
					re = cc.iterator().next();
					if (re != null) {
						reList.add(String.valueOf(re));
					}
				}
				
			}
		}
		return reList;
	}

	public static Object queryForValue(Connection conn, String sql, Object[] params)
			throws Exception {
		Map resultMap = queryForMap(conn, sql, params);
		if (resultMap == null) {
			return null;
		}
		Collection cc = resultMap.values();
		Object re = null;
		if (cc != null && !cc.isEmpty()) {
			re = cc.iterator().next();
		}
		return re;
	}

	public static Map queryForMap(Connection conn, String sql, Object[] params) throws Exception {
		List<Map<String, Object>> list = getQueryMap(conn, sql, params);
		Map re = null;
		if (list != null && list.size() > 0) {
			re = (Map) list.get(0);
		}
		return re;
	}

	public static List<Map<String, Object>> getQueryMap(Connection conn, String sql, Object[] params)
			throws Exception {
		PreparedStatement ptmt = null;
		ResultSet rset = null;
		List list = new ArrayList();
		try {
			ptmt = conn.prepareStatement(sql);
			log.debug(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					setParamValue(ptmt, i + 1, params[i]);
					log.debug(params[i]);
				}
			}
			rset = ptmt.executeQuery();
			ResultSetMetaData rsmd = rset.getMetaData();
			int cols = rsmd.getColumnCount();

			while (rset.next()) {
				Object object = null;
				Map map = new LinkedHashMap<String, Object>();
				for (int i = 1; i <= cols; i++) {
					String columnTypeName = rsmd.getColumnTypeName(i);
					if ("DATE".equals(columnTypeName)) {
						map.put(rsmd.getColumnName(i).toLowerCase(), rset.getTimestamp(i));
					} else {
						map.put(rsmd.getColumnName(i).toLowerCase(), rset.getObject(i));
					}

				}
				list.add(map);
			}
		} finally {
			try {
				if (rset != null)
					rset.close();
			} catch (Throwable se) {
			}

			//
			try {
				if (ptmt != null)
					ptmt.close();
			} catch (Throwable se) {
			}

			try {
				if (conn != null)
					conn.close();
			} catch (Throwable se) {
			}
		}
		return list;
	}

	private static void setParamValue(PreparedStatement Pstmt, int index, Object obj)
			throws SQLException {
		if (obj != null) {
			String fieldtype = obj.getClass().getName();
			fieldtype = fieldtype == null ? "" : fieldtype;
			if (fieldtype.equals("java.lang.String"))
				Pstmt.setString(index, (String) obj);
			else if (fieldtype.equals("java.lang.Integer")) {
				Integer k = (Integer) obj;
				Pstmt.setInt(index, k.intValue());
			} else if (fieldtype.equals("java.lang.Double")) {
				Double k = (Double) obj;
				Pstmt.setDouble(index, k.doubleValue());
			} else if (fieldtype.equals("java.lang.Long")) {
				Long k = (Long) obj;
				Pstmt.setLong(index, k.longValue());
			} else if (fieldtype.equals("java.util.Date")) {
				Pstmt.setTimestamp(index, new java.sql.Timestamp(((java.util.Date) obj).getTime()));
			} else if (fieldtype.equals("java.sql.Date")) {
				Pstmt.setDate(index, (Date) obj);
			} else if (fieldtype.equals("java.sql.Timestamp")) {
				Pstmt.setTimestamp(index, (Timestamp) obj);
			} else if (obj instanceof java.io.File) {
				File ff = (java.io.File) obj;
				InputStream is = null;
				try {
					is = new FileInputStream(ff);
				} catch (FileNotFoundException e) {
					Map params = new HashMap();
					params.put("file_path", ff.getAbsolutePath());
				}
				Pstmt.setBinaryStream(index, is, (int) (ff.length()));
			} else
				Pstmt.setString(index, (String) obj);
		} else
			Pstmt.setObject(index, null);
	}

	public static Object[] exeProcedure(Connection conn, String sql, Object[] inParams,
			Object[] outParams) {
		CallableStatement cstmt = null;
		Object[] params = new Object[inParams.length + outParams.length];
		for (int i = 0; i < inParams.length; i++) {
			params[i] = inParams[i];
		}
		for (int j = inParams.length; j < inParams.length + outParams.length; j++) {
			params[j] = outParams[j - inParams.length];
		}
		log.debug(genDebugSqlString(sql, params));
		boolean tmp = false;
		try {
			cstmt = conn.prepareCall(sql);
			if (inParams != null) {
				for (int i = 0; i < inParams.length; i++) {
					setParamValue(cstmt, i + 1, inParams[i]);
				}
			}
			if (outParams != null) {
				for (int j = inParams.length; j < inParams.length + outParams.length; j++) {
					registerOutParameter(cstmt, j + 1, outParams[j - inParams.length]);
				}
			}
			cstmt.execute();
			for (int j = inParams.length; j < inParams.length + outParams.length; j++) {
				outParams[j - inParams.length] = getOutParameterValue(cstmt, j + 1, outParams[j
						- inParams.length]);
			}
			tmp = true;
		} catch (Exception e) {
			tmp = false;
			Map paramMap = new HashMap();
			paramMap.put("sql", sql);
			paramMap.put("params", params);
			paramMap.put("msg", e.getMessage());
			e.printStackTrace();
		} finally {
			close(conn, cstmt, null);
		}

		return outParams;
	}

	public static String genDebugSqlString(String sql, Object[] params) {
		StringBuffer sb = new StringBuffer();
		sb.append("\n===sql[").append(sql).append("]");
		sb.append("\n===params:[");
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				sb.append(params[i]);
				if (i != params.length - 1) {
					sb.append(",");
				}
			}
		}
		sb.append("]");
		sb.append("\n===sql with params:[");
		sb.append(genDebugSqlStringWithParams(sql, params));
		sb.append("]");
		return sb.toString();
	}

	public static String genDebugSqlStringWithParams(String sql, Object[] params) {
		StringBuffer sb = new StringBuffer();
		try {
			sql += " ";
			String[] sqlSplitArray = sql.split("\\?");
			if (sqlSplitArray.length == 1) {
				sb.append(sql);
			} else {
				for (int i = 0; i < sqlSplitArray.length - 1; i++) {
					sb.append(sqlSplitArray[i]).append("'").append(params[i]).append("'");
				}
				sb.append(sqlSplitArray[sqlSplitArray.length - 1]);
			}
		} catch (Exception e) {
			log.error("", e);
		}
		return sb.toString().trim();
	}

	public static void close(Connection conn, PreparedStatement stmt, ResultSet rs) {
		//
		try {
			if (rs != null)
				rs.close();
		} catch (Throwable se) {
		}

		//
		try {
			if (stmt != null)
				stmt.close();
		} catch (Throwable se) {
		}

		try {
			if (conn != null)
				conn.close();
		} catch (Throwable se) {
		}
	}

	private static void registerOutParameter(CallableStatement cstmt, int index, Object obj)
			throws SQLException {
		if (obj != null) {
			String fieldtype = obj.getClass().getName();
			fieldtype = fieldtype == null ? "" : fieldtype;
			if (fieldtype.equals("java.lang.String"))
				cstmt.registerOutParameter(index, Types.VARCHAR);
			else if (fieldtype.equals("java.lang.Integer")) {
				cstmt.registerOutParameter(index, Types.INTEGER);
			} else if (fieldtype.equals("java.lang.Double")) {
				cstmt.registerOutParameter(index, Types.DOUBLE);
			} else if (fieldtype.equals("java.lang.Long")) {
				cstmt.registerOutParameter(index, Types.INTEGER);
			} else if (fieldtype.equals("java.util.Date")) {
				cstmt.registerOutParameter(index, Types.DATE);
			} else if (fieldtype.equals("java.sql.Date")) {
				cstmt.registerOutParameter(index, Types.DATE);
			} else if (fieldtype.equals("java.sql.Timestamp")) {
				cstmt.registerOutParameter(index, Types.TIMESTAMP);
			} else
				cstmt.registerOutParameter(index, Types.VARCHAR);
		} else
			cstmt.registerOutParameter(index, Types.VARCHAR);
	}

	private static Object getOutParameterValue(CallableStatement cstmt, int index, Object obj)
			throws SQLException {
		if (obj != null) {
			String fieldtype = obj.getClass().getName();
			fieldtype = fieldtype == null ? "" : fieldtype;
			if (fieldtype.equals("java.lang.String"))
				obj = cstmt.getString(index);
			else if (fieldtype.equals("java.lang.Integer")) {
				obj = cstmt.getInt(index);
			} else if (fieldtype.equals("java.lang.Double")) {
				obj = cstmt.getDouble(index);
			} else if (fieldtype.equals("java.lang.Long")) {
				obj = cstmt.getLong(index);
			} else if (fieldtype.equals("java.util.Date")) {
				obj = cstmt.getDate(index);
			} else if (fieldtype.equals("java.sql.Date")) {
				obj = cstmt.getDate(index);
			} else if (fieldtype.equals("java.sql.Timestamp")) {
				obj = cstmt.getTimestamp(index);
			} else
				obj = cstmt.getString(index);
		} else
			obj = cstmt.getString(index);
		return obj;
	}
	
	/**
	 * 解决IN子句内元素超过1000
	 * @param column
	 * @param params
	 * @return
	 */
	public static String buildInClause(String column, List<String> params) {
		StringBuffer clause = new StringBuffer();
		
		if (params != null && params.size() > 0) {
			int j = 0;
			int step = 500;
			int len = params.size();
			clause.append(" ( ");
			while (j < len) {
				if (j > 0) {
					clause.append(" or ");
				}
				
				if ((j + step) >= len) {
					clause.append(column + " in (").append(ConvertUtils.convert(params.subList(j, len))).append(") ");
				} else {
					clause.append(column + " in (").append(ConvertUtils.convert(params.subList(j, (j + step)))).append(") ");
				}
				
				j += step;
			}
			
			clause.append(" ) ");
		}
		
		return clause.toString();
	}
}
